#coding: utf-8
import openpyxl

def loadUserInfoData(fileName):
    wb = openpyxl.load_workbook(fileName)
    ws = wb.active
    result = []
    for r in range(2, ws.max_row + 1):
        tmpDict = {}
        for c in range(1, ws.max_column + 1):
            tmpValue = ws.cell(row = r, column = c).value
            if c == 1:
                tmpDict["name"] = tmpValue
            elif c == 2:
                tmpDict["mobile"] = tmpValue
            elif c == 3:
                tmpDict["certificateNo"] = tmpValue
            elif c == 4:
                tmpDict["customerId"] = tmpValue
        result.append(tmpDict)
    return result

def readXlsFileData(fileName):
    # 打开excel文件,获取工作簿对象
    wb = openpyxl.load_workbook(fileName)
    # 从表单中获取单元格的内容
    ws = wb.active  # 当前活跃的表单
    # 将读取到的数据存入内存中（数据较小）
    baseData = []
    for r in range(2, ws.max_row + 1):
        mobile = ""
        tmpDict = {}
        for c in range(1, ws.max_column + 1):
            # print("row is {}, column is {}, value is {}".format(r, c, ws.cell(row=r, column=c).value))
            tmpData = ws.cell(row=r, column=c).value
            if c == 4:
                tmpDict["address"] = tmpData
            elif c == 1:
                tmpDict["mobile"] = tmpData
                mobile = tmpData
            elif c == 2:
                tmpDict["name"] = tmpData
            elif c == 3:
                tmpDict["czjh"] = tmpData
            elif c == 5:
                tmpDict["code"] = tmpData
            elif c == 6:
                tmpDict["start"] = tmpData.__str__()[0:10]  # .__format__("%Y/%m/%d")
            elif c == 7:
                tmpDict["end"] = tmpData.__str__()[0:10]
            elif c == 8:
                tmpDict["ll"] = tmpData
            elif c == 9:
                tmpDict["lx"] = tmpData
            elif c == 10:
                tmpDict["total"] = tmpData
        baseData.append(tmpDict)
        # print("line is {}".format(r))
    return baseData